Create database iCMS
--Drop database iCMS
go
use iCMS
go
CREATE TABLE user_group
(
	user_group_id int identity(1,1) PRIMARY KEY,
	name nvarchar(30),
	permission text
)
GO
create table [user]
(
	[user_id] int identity(1,1) Primary key,
	[user_name] nvarchar(50),
	[password] nvarchar(50),
	[full_name] nvarchar(50),
	[birthday] datetime,
	[email] nvarchar(50),
	[sex] bit,
	[address] nvarchar(255),
	[phone] nvarchar(50),
	[user_group_id] int foreign key references user_group(user_group_id),
	[date_added] datetime default Getdate()
)

GO
CREATE TABLE [language]
(
	language_id int identity(1,1) PRIMARY KEY,
	name nvarchar(10),
	code varchar(10),
	locale varchar(20),
	[image] nvarchar(20)
)
go
create table category
(
	category_id int identity(1,1) primary key,
	parent_id int Default '0',
	[status] bit default 'true',
	date_added datetime default getdate()
)
GO
CREATE TABLE category_description
(
	category_id int,
	language_id int REFERENCES [language](language_id),
	name nvarchar(30) not null,
	meta_description nvarchar(50),
	date_added datetime default getdate(),
	Primary key(category_id,language_id)
	
)

go
create table [Topic]
(
	topic_id int identity(1,1) primary key,
	topic_name nvarchar(50),
	topic_des nvarchar(255),
	date_added datetime default Getdate()
)
go
create table article
(
	article_id int identity(1,1) primary key,
	[user_id] int foreign key references [User]([user_id]),
	[image] nvarchar(50),
	category_id int foreign key references [Category](category_id),
	topic_id int foreign key references [Topic](topic_id),
	[date_added] datetime default Getdate(),
	[status] bit
)
go
CREATE TABLE article_description
(
	article_id int REFERENCES article(article_id),
	language_id int REFERENCES [language](language_id),
	article_name nvarchar(50),
	article_sumary nvarchar(255),
	article_des text	
)
Go

create table [Comment]
(
	comment_id int identity(1,1) primary key,
	article_id int foreign key references [Article](article_id),
	comment_name nvarchar(50),
	comment_des text,
	email nvarchar(50),
	[status] bit,
	[date_added] datetime default Getdate()
)
GO
-- CREATE PROC INSERT COMMENT
Create proc IS_Comment
@comment_id int = null,
@article_id int = null,
@comment_name nvarchar(50) = null,
@comment_des text = null,
@email nvarchar(50) = null,
@status bit = 'false'
AS
	BEGIN
		DECLARE @row int
		DECLARE @err int
		BEGIN TRANSACTION
			UPDATE
				Comment
			set
				article_id = @article_id,
				comment_name = @comment_name,
				comment_des = @comment_des,
				[status] = @status
			where
				comment_id = @comment_id
				OR email = @email
			SELECT @row = @@ROWCOUNT , @err = @@ERROR
			IF(@row = 0)
				BEGIN
					INSERT INTO
						Comment
					Values
					(
						@article_id,
						N''+@comment_name,
						@comment_des,
						@email,
						@status,
						GETDATE()
					) 
				END
				ELSE IF @err <> 0
				BEGIN
					ROLLBACK TRANSACTION
					RETURN
				END
		COMMIT TRANSACTION
			
	END
	
GO
Create proc SL_Comment
@comment_id int  = null,
@article_id int  = null,
@email nvarchar(50) = null
AS
	BEGIN
		IF(@comment_id IS NULL AND @email IS NULL AND @article_id IS NULL)
			BEGIN
				SELECT 
				*
				FROM
				Comment
			END
		ELSE IF @article_id IS NOT NULL AND (@comment_id IS NULL AND @email IS NULL)
			BEGIN
			SELECT 
			*
			FROM 
			Comment
			WHERE 
			article_id = @article_id
			END
		ELSE IF @article_id IS NULL
			BEGIN
			SELECT
			*
			FROM 
			Comment
			WHERE
			comment_id = @comment_id
			OR email = @email	
			END
	END
GO

ALTER PROC GET_CATEGORY
@category_id int = null,
@parent_id int = null,
@language_id int = null,
@status bit =null
AS
	IF(@category_id is not null AND @status is not null AND @language_id is null )
		BEGIN
			SELECT * 
			FROM category 
			WHERE category_id=@category_id AND [status]=@status 			
		END
		
	ELSE IF(@category_id is null AND @parent_id is not null AND  @language_id is not null AND @status is not null)
		BEGIN 
			SELECT * 
			FROM category c LEFT JOIN category_description cd
			ON c.category_id=cd.category_id
			WHERE c.parent_id=@parent_id AND cd.language_id=@language_id AND [status]=@status
			ORDER BY cd.name
			
		END
	ELSE IF(@category_id is not null AND @language_id is null AND @status is null AND @parent_id is null)
		BEGIN 
			SELECT * 
			FROM category_description 
			WHERE category_id=@category_id
			
		END

	ELSE IF(@category_id is not null AND @language_id is not null AND @status is not null AND @parent_id is null)
	BEGIN
		SELECT * FROM category c
		LEFT JOIN category_description cd
		ON c.category_id=cd.category_id
		WHERE c.category_id=@category_id AND cd.language_id=@language_id AND c.[status]=@status
	END 
	ELSE IF(@category_id is null and @parent_id is null and @language_id is null and @status is null )
		BEGIN
			SELECT * FROM category_description
		END

		
	
	ELSE IF(@category_id is null AND @language_id is not null AND @status is not null AND @parent_id is null)
	BEGIN
		SELECT * FROM category c
		LEFT JOIN category_description cd
		ON c.category_id=cd.category_id
		WHERE  cd.language_id=@language_id AND c.[status]=@status
		ORDER BY cd.category_id ASC
	END
	IF(@category_id is null AND @parent_id is not null AND @language_id is null AND @status is null)
	BEGIN
		SELECT * FROM category WHERE parent_id=@parent_id
	END
		
GO
CREATE PROC GET_LASTID
@category_id int=null
AS
	IF(@category_id is not null)
	BEGIN
		SELECT Top 1 * FROM category_description ORDER BY date_added DESC	
	END

GO

ALTER PROC EDIT_CATEGORY
@category_id int =null,
@language_id int=null,
@parent_id int =null,
@name nvarchar(30)=null,
@meta_description text=null,
@date_added datetime,
@status bit=null

AS

BEGIN
	IF(@category_id is not null AND @parent_id is not null AND @status is not null AND @date_added is not null)
	BEGIN 
		IF((SELECT COUNT(*) FROM category WHERE category_id=@category_id)!=0)
		BEGIN 
			UPDATE category SET parent_id=@parent_id,[status]=@status,date_added=@date_added WHERE category_id=@category_id
		END ELSE 
		BEGIN
			INSERT category(parent_id,[status],date_added) VALUES(@parent_id,@status,@date_added)
		END
	END
	IF(@category_id is not null AND @language_id is not null AND @name is not null AND @meta_description is not null AND @date_added is not null)
	BEGIN
		IF((SELECT COUNT(*) FROM category_description WHERE category_id=@category_id AND language_id=@language_id)!=0)
		BEGIN 
			UPDATE category_description SET name=@name,meta_description=@meta_description,date_added=@date_added WHERE category_id=@category_id AND language_id=@language_id
		END
		ELSE
		BEGIN
			INSERT category_description(category_id,language_id,name,meta_description,date_added) VALUES(@category_id,@language_id,@name,@meta_description,GETDATE())
		END
	END
	IF(@category_id is not null AND @language_id is null AND @name is null AND @meta_description is null AND @date_added is null)
	BEGIN
		IF((SELECT COUNT(*) FROM article WHERE category_id=@category_id)!=0)
		BEGIN
			UPDATE article SET category_id=NULL WHERE category_id=@category_id
		END
		DELETE FROM category_description WHERE category_id=@category_id
		DELETE FROM category WHERE category_id=@category_id
		
	END
END
 
GO
CREATE PROC GET_LANGUAGE
@language_id int =null,
@name nvarchar(10)=null,
@code  varchar(10)=null,
@locale varchar(20)=null,
@image nvarchar(20)=null
AS
	IF(@language_id	is not null AND @name is not null AND @code is not null AND @locale is not null AND @image is not null)
	BEGIN
		if((SELECT COUNT(*) FROM [language] WHERE language_id=@language_id)!=0)
		BEGIN
			UPDATE [language] SET name=@name,code=@code,locale=@locale,[image]=@image WHERE language_id=@language_id
		END
	END 
	ELSE IF(@name is not null AND @code is not null AND @locale is not null AND @image is not null)
	BEGIN
		INSERT [language](name,code,locale,[image]) VALUES(@name,@code,@locale,@image)
	END
	ELSE IF(@language_id is not null)
	BEGIN
		SELECT * FROM [language] WHERE language_id=@language_id
	END
	



	GO		
Create Proc SL_CommentIsTrue
@article_id int = null
AS
BEGIN
SELECT 
*
FROM
Comment
WHERE 
article_id = @article_id
AND [status] = 'true'
END 
GO
Create proc SL_EmailbyUserName
@user_name nvarchar(50) = null
AS
	BEGIN
		SELECT 
			email
		FROM
			[User]
		WHERE 
			[user_name] = @user_name
	END

GO

--INSERT Article
CREATE PROC INSERT_Article
@user_id int,
@image nvarchar(50),
@category_id int,
@topic_id int,
@status bit = 'false',
@language_id int,
@article_name nvarchar(50),
@article_sumary nvarchar(255),
@article_des text
AS
BEGIN
INSERT INTO article VALUES (@user_id,@image,@category_id,@topic_id,GETDATE(),@status)
INSERT INTO article_description VALUES(SCOPE_IDENTITY(),@language_id,N''+@article_name,N''+@article_sumary,@article_des)
END

GO

--UPDATE Article
CREATE PROC UPDATE_Article
@article_id int,
@user_id int,
@image nvarchar(50),
@category_id int,
@topic_id int,
@status bit,
@language_id int,
@article_name nvarchar(50),
@article_sumary nvarchar(255),
@article_des text
AS
BEGIN
UPDATE article SET [user_id] = @user_id,[image]=@image,category_id=@category_id,topic_id=@topic_id,status=@status WHERE article_id=@article_id
UPDATE article_description SET article_name = @article_name,article_sumary=@article_sumary,article_des=@article_des WHERE article_id=@article_id AND language_id = @language_id
END

GO

--VIEW Article
ALTER VIEW VW_Article
AS
SELECT     a.article_id, c.category_id, t.topic_id, ad.article_name, ad.article_sumary, ad.article_des, a.image, a.date_added, a.status, u.user_name, c.parent_id, t.topic_name, 
                      dbo.category_description.name
FROM         dbo.article AS a INNER JOIN
                      dbo.[User] AS u ON a.user_id = u.user_id INNER JOIN
                      dbo.category AS c ON a.category_id = c.category_id INNER JOIN
                      dbo.Topic AS t ON a.topic_id = t.topic_id INNER JOIN
                      dbo.article_description AS ad ON a.article_id = ad.article_id INNER JOIN
                      dbo.category_description ON a.category_id = dbo.category_description.category_id

GO	         


GO



--SELECT VIEW Article
CREATE PROC [dbo].[SL_View_Article]
@article_id int = null,
@category_id int = null,
@topic_id int = null
AS
BEGIN
	IF(@article_id IS NULL AND @category_id IS NULL AND @topic_id IS NULL)
		BEGIN
			SELECT * FROM VW_Article
		END
	ELSE IF @article_id IS NOT NULL AND( @category_id IS NULL AND @topic_id IS NULL)
		BEGIN
			SELECT * FROM VW_Article WHERE article_id = @article_id
		END
	ELSE IF @category_id IS NOT NULL AND( @article_id IS NULL AND @topic_id  IS NULL)
		BEGIN
			SELECT * FROM VW_Article WHERE category_id = @category_id
		END
	ELSE IF @topic_id IS NOT NULL AND( @article_id IS NULL AND @category_id IS NULL)
		BEGIN
			SELECT * FROM VW_Article WHERE topic_id = @topic_id
		END
END


GO

--SELECT Topic
CREATE PROC SL_Topic
AS
BEGIN
SELECT * FROM Topic
END

GO

CREATE PROC EDIT_USER_GROUP
@user_group_id int =null,
@name nvarchar(30)=null,
@permission text=null
AS
	IF(@user_group_id is not null AND @name is not null AND @permission is not null)
	BEGIN
		IF((SELECT COUNT(*) FROM user_group WHERE user_group_id=@user_group_id)!=0)
		BEGIN
			UPDATE user_group SET name=@name,permission=@permission WHERE user_group_id=@user_group_id 
		END ELSE		
		BEGIN
			INSERT INTO user_group(name,permission) VALUES(@name,@permission)
		END
	END
	ELSE IF(@user_group_id is not null)
	BEGIN
		IF((SELECT COUNT(*) FROM [user] WHERE user_group_id=@user_group_id)=0)
		BEGIN
			DELETE FROM user_group WHERE user_group_id=@user_group_id
		END
	END
	
GO
CREATE PROC GET_USERGROUP
@user_group_id int =null,
@name nvarchar(30)=null,
@permission text=null
AS
IF(@user_group_id is not null)
BEGIN
	SELECT * FROM user_group WHERE user_group_id=@user_group_id	
END
ELSE 
	SELECT * FROM user_group
go
Create PROC IS_User
@user_id int = null,
@user_name nvarchar(50) = null,
@password nvarchar(50) = null,
@full_name nvarchar(50) = null,
@birthday datetime = null,
@email nvarchar(50) = null,
@sex bit = null,
@address nvarchar(255) = null,
@phone nvarchar(50) = null,
@user_group_id int = null
AS
	BEGIN
		DECLARE @row int
		DECLARE @err int
		BEGIN TRANSACTION
		Update
		[User]
		set
		[user_name] = N''+@user_name,
		[password] = N''+@password,
		[full_name] = N''+@full_name,
		birthday = @birthday,
		email = N''+@email,
		sex = @sex,
		[address] = N''+@address,
		[phone] = @phone,
		user_group_id = @user_group_id
		Where
		[user_id] = @user_id
		Or [user_name] = @user_name
		SELECT @row = @@ROWCOUNT , @err = @@ERROR
			IF(@row = 0)
				BEGIN
					INSERT INTO
						[User]
					Values
					(
						@user_name,
						@password,
						N''+@full_name,
						@birthday,
						N''+@email,
						@sex,
						N''+@address,
						@phone,
						@user_group_id,
						GETDATE()
					) 
				END
				ELSE IF @err <> 0
				BEGIN
					ROLLBACK TRANSACTION
					RETURN
				END
		COMMIT TRANSACTION
	END
GO
Create PROC [Login]
@user_name nvarchar(50) = null,
@password nvarchar(50) = null
AS
	BEGIN
	SELECT
	*
	FROM
	[User]
	WHERE
	[user_name] = @user_name
	AND [password] = @password
	END
go
Create PROC SL_User
@user_id int = null,
@user_name nvarchar(50) = null,
@email nvarchar(50) = null
AS
BEGIN
	IF(@user_id IS NULL OR @user_name IS NULL OR @email IS NULL)
	BEGIN
		SELECT
		*
		FROM
		[User]
	END
	ELSE IF(@user_id IS NOT NULL)
	Begin
		SELECT 
		*
		FROM 
		[User]
		WHERE
		[user_id] = @user_id
	end
	ELSE IF(@user_id IS NULL)
	begin
		SELECT
		* 
		FROM
		[User]
		Where
		[user_name] = @user_name
		Or
		email = @email
	end
END